package hittegods.classes;

import hittegods.database.MySQLConn;
import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;
import javax.swing.JComboBox;

public class Item {

    public final static int SUCCESS = 1;
    public final static int ERROR = 2;
    public final static int SQL_EXCEPTION = 3;
    public final static int UNDEFINED_ERROR = 4;
    private int error;
    private String caseNo;
    private int categoryId;
    private String description;
    private Date handinDate;
    private Date foundDate;
    private String foundPlace;
    private String finderName;
    private String ownerName;
    private Date returnedDate;
    private boolean returned;
    private int status;
    private Reward reward;

    public Item(String caseNo) {
        this.caseNo = caseNo;
        reward = new Reward(caseNo);
        try {
            Statement stmt = MySQLConn.getConnection().createStatement();
            String sql = "SELECT * FROM item WHERE caseNo='" + caseNo + "'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                this.categoryId = rs.getInt("categoryId");
                this.description = rs.getString("description");
                this.handinDate = rs.getDate("handinDate");
                this.foundDate = rs.getDate("foundDate");
                this.foundPlace = rs.getString("foundPlace");
                this.finderName = rs.getString("finderName");
                this.ownerName = rs.getString("ownerName");
                this.returnedDate = rs.getDate("returnedDate");
                this.returned = rs.getBoolean("returned");
                this.status = rs.getInt("statusId");
            }
            rs.close();
        } catch (Exception ex) {
        }
    }

    public Item() {
    }

    @Override
    public String toString() {
        return caseNo + " / " + description + ", " + handinDate + ", " + foundDate + ", " + foundPlace + ", " + finderName + ", " + ownerName + ", " + returnedDate + ", " + returned + ", " + status;
    }

    public static int insertItem(String caseNo, int categoryId, String description, Date foundDate, String foundPlace, String finderName, String ownerName, String reward) {
        int result = 4;
        try {
            String sql = "INSERT INTO item (caseNo,categoryId,description,foundDate,foundPlace, finderName,ownerName) VALUES( " + "'" + caseNo + "'," + categoryId + "," + "'" + description + "'," + "'" + foundDate + "'," + "'" + foundPlace + "'," + "'" + finderName + "'," + "'" + ownerName + "'" + ")";
            Statement stmt = MySQLConn.getConnection().createStatement();
            if (stmt.execute(sql)) {
                result = 1;
            } else {
                result = 3;
            }
        } catch (SQLException ex) {
            return 3;
        } catch (Exception ex) {
            return 4;
        }
        return result;
    }

    public int editItem(int categoryId, String description, Date foundDate, String foundPlace, String finderName, String ownerName, Date returnedDate, boolean returned, int status) {
        int result = 4;
        try {
            String sql;
            if(returnedDate == null){
                sql = "UPDATE item SET categoryId=" + categoryId + ", description='" + description + "', foundDate='" + foundDate + "', foundPlace='" + foundPlace + "', finderName='" + finderName + "',ownerName='" + ownerName + "',returnedDate=" + returnedDate + ", returned=" + returned + ",statusId=" + status + " WHERE caseNo='" + this.caseNo + "'";
            }else{
                sql = "UPDATE item SET categoryId=" + categoryId + ", description='" + description + "', foundDate='" + foundDate + "', foundPlace='" + foundPlace + "', finderName='" + finderName + "',ownerName='" + ownerName + "',returnedDate='" + returnedDate + "', returned=" + returned + ",statusId=" + status + " WHERE caseNo='" + this.caseNo + "'";
            }
            System.out.println(sql);
            Statement stmt = MySQLConn.getConnection().createStatement();
            stmt.execute(sql);
            result = 1;
        } catch (SQLException ex) {
            return 3;
        } catch (Exception ex) {
            result = 4;
        }
        return result;
    }

    public int deleteItem() {
        String sql = "UPDATE item SET statusId=2 WHERE caseNo='" + this.caseNo + "'";
        int result = 4;
        try {
            Statement stmt = MySQLConn.getConnection().createStatement();
            stmt.executeUpdate(sql);
            result = 1;
        } catch (SQLException ex) {
            return 3;
        } catch (Exception ex) {
            return 4;
        }
        return result;
    }

    public static JComboBox createCatComboBox() {

        ArrayList<Category> catList = new ArrayList<Category>();
        ArrayList<Category> subCatList = new ArrayList<Category>();

        /**
         * Select everything from category, create category objects for every
         * row and add them to the category list.
         */
        try {
            String sqlCat = "SELECT * FROM category";
            Statement stmt = MySQLConn.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(sqlCat);
            while (rs.next()) {
                int catId = rs.getInt("categoryId");
                String catName = rs.getString("categoryName");
                catList.add(new Category(catId, catName));
            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
        } catch (Exception ex) {
        }
        /**
         * Select everything from subcategory, create category objects for every
         * row and add them to the category list.
         */
        try {
            String sqlSubCat = "SELECT * FROM subCat";
            Statement stmt = MySQLConn.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(sqlSubCat);
            while (rs.next()) {
                int catId = rs.getInt("categoryId");
                String catName = rs.getString("categoryName");
                int parentCatId = rs.getInt("parentCatId");
                subCatList.add(new Category(catId, catName, parentCatId));
            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
        } catch (Exception ex) {
        }

        //Create empty JComboBox
        JComboBox comboBox = new JComboBox();

        comboBox.addItem(new Category(0, "Vælg en kategori"));
        //Go through the catList and add the category item to the combobox.
        for (int i = 0; i < catList.size(); i++) {
            //add the uppercategory to the list
            comboBox.addItem(catList.get(i));
            for (int j = 0; j < subCatList.size(); j++) {
                //if the category has subcategories, add them before going to next iteration.
                if (catList.get(i).getCatId() == subCatList.get(j).getParentId()) {
                    comboBox.addItem(subCatList.get(j));
                }
            }
        }


        return comboBox;
    }

    public String getCaseNo() {
        return caseNo;
    }

    public int getError() {
        return error;
    }

    private void setError(int error) {
        this.error = error;
    }

    public int getCategoryId() {
        return categoryId;
    }

    public String getDescription() {
        return description;
    }

    public String getFinderName() {
        return finderName;
    }

    public Date getFoundDate() {
        return foundDate;
    }

    public String getFoundPlace() {
        return foundPlace;
    }

    public Date getHandinDate() {
        return handinDate;
    }

    public String getOwnerName() {
        return ownerName;
    }

    public boolean isReturned() {
        return returned;
    }

    public Date getReturnedDate() {
        return returnedDate;
    }

    public Reward getReward() {
        return reward;
    }

    public int getStatus() {
        return status;
    }

    public static class Category {

        private int catId;
        private String catName;
        private int parentId;

        /**
         * Constructor for subcategories!
         * @param catId
         * @param catName
         * @param parentId
         */
        public Category(int catId, String catName, int parentId) {
            this.catId = catId;
            this.catName = catName;
            this.parentId = parentId;
        }

        /**
         * Constructor for parentcategories!
         * @param catId
         * @param catName
         */
        public Category(int catId, String catName) {
            this.catId = catId;
            this.catName = catName;
            this.parentId = 0;
        }

        public int getCatId() {
            return catId;
        }

        public String getCatName() {
            return catName;
        }

        public int getParentId() {
            return parentId;
        }

        @Override
        public String toString() {
            if (parentId == 0) {
                return catName;
            } else {
                return "   " + catName;
            }

        }
    }

    public static class ItemComparator implements Comparator {

        private int sortAtt;
        private boolean desc;

        public ItemComparator(int sortAtt) {
            this.sortAtt = sortAtt;
        }

        @Override
        public int compare(Object i1, Object i2) {
            int result = 0;
            Item item1 = (Item) i1;
            Item item2 = (Item) i2;

            switch (sortAtt) {
                case 0:
                    result = item1.getCaseNo().compareTo(item2.getCaseNo());
                    break;
                case 1:
                    if (item1.getCategoryId() > item2.getCategoryId()) {
                        result = 1;
                    } else if (item1.getCategoryId() < item2.getCategoryId()) {
                        result = -1;
                    } else {
                        result = 0;
                    }
                    break;
                case 2:
                    result = item1.getFoundDate().compareTo(item2.getFoundDate());
                    break;
                case 3:
                    result = item1.getFoundPlace().compareTo(item2.getFoundPlace());
                    break;
                case 4:
                    result = item1.getFinderName().compareTo(item2.getFinderName());
                    break;
                case 5:
                    result = item1.getOwnerName().compareTo(item2.getOwnerName());
                    break;
                default:
                    throw new AssertionError();
            }

            return result;
        }
    }
}
